from util.common import random_string, render_template, write_file
from db.db_util import dataframe_from_db

if __name__ == '__main__':

    df = dataframe_from_db("""
        SELECT * FROM TF_SA_SEEP_HOLE sh 
        LEFT JOIN TF_SA_SEEP_REPORT ssr on sh.SEEP_REPORT_ID = ssr.SEEP_REPORT_ID
        WHERE sh.SEEP_HOLE_ID like 'SEEPZX%'
    """)

    list = []
    for row in df.itertuples():
        id = "WEREZX" + random_string(14)
        vid = row.SEEP_HOLE_ID
        name = row.HOLE_NAME
        level = row.SAFETY_LEVEL
        assetId = row.WEB_ASSET_ID
        startDate = row.CREATE_TIME
        assetName = None

        seep = {
            "id": id,
            "name": name,
            "vid": vid,
            "assetId": assetId,
            "level": level,
            "startDate": startDate,
            "assetName": assetName,
        }
        message1 = render_template("""
                       INSERT INTO tf_sa_web_result (WEB_ID, web_name, web_desc, RISK_LEVEL, SOURCE, WEB_TYPE,
                                      BUSINESS_ID, tips, ASSET_IP, asset_url, ASSET_TYPE, PARTNER,
                                      WEB_DEPT, APPMAN_DEPT, APPMAN_BOSSRES, APPDEV_FACRES, APPDEV_BOSSRES,
                                      APPMAN_FAC, APPMAN_FACRES, SYS_FACRES, SYS_BOSSRES, is_onlinepj,
                                      online_id, online_cycle, testreport_id, menu_id, menu_path, IS_ON_NET,
                                      FIND_DATE, FIND_TYPE, FINDER, IS_TESTLOGIN, SENDER, is_sended,
                                      SEND_DATE, change_id, CLOSE_DATE, changebuss_reqid, changesoft_reqid,
                                      vuln_reason, devolep_desc, is_wrong, REINFORCE_PLANDATE, REINFORCER,
                                      REINFORCE_DATE, reinforce_status, reinforce_desc, reinforce_yx,
                                      appboss_opin, sysboss_opin, fac_opin, REVIEW_RESULT, REVIEW_DATE,
                                      REVIEWER, report_status, REPORT_DATE, rebuid_advice, FACRES, BOSSRES,
                                      OPT_ID, TIMES, REPORT_NUM, TEST_REPORTNUM, ASSET_ID, ASSET_STATUS,
                                      BEAR_BUSS, HOLE_ENCODE, review_attach, REVIEW_PROPOSAL, remark,
                                      RETREAT_ADVICE, FACDEAL, asset_name, TASK_ID, IS_IGNORE,
                                      REINFORCE_DELAY, REINFORCE_LINE_DATE, category, CVE_ID, CNCVE_ID,
                                      CNVD_ID, CNNVD_ID, AFFECT_URL, VULN_CHECK_RESULT, VULN_PRIORITY_SCORE,
                                      LABEL, LABEL_NAME, HOLE_RULE, STATUS, VPT_LEVEL, THREAT_LEVEL,
                                      UPLOAD_VULN_STATUS, PUB_DATE, THREAT_CATEGORY, IS_JOINPRO, DEAL_USER,
                                      IP_VAL, VALIDATE_START_TIME, VALIDATE_END_TIME, RETEST_STATUS,
                                      REPAIRE_STATUS, RETEST_DESC, RETEST_USER, RETEST_TIME, VULN_ID)
            VALUES ($id, $name,
                    NULL,
                    $level, '渗透漏洞', '0', '0', NULL, NULL, 'http://10.187.15.234:8080/portal/record/recordTemplatePageList.action',
                    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '-1', NULL, NULL, NULL, NULL, NULL, '1',
                    $startDate, '1', '0', '0', NULL, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                    NULL, '-1', NULL, NULL, NULL, NULL, NULL, '-1', NULL, NULL, NULL, NULL,
                    NULL,
                    NULL, NULL, 'OPT08532906227645021', 1, NULL, NULL, $assetId, NULL, NULL, NULL, NULL, NULL, NULL,
                    NULL, NULL, $assetName, NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                    'http://10.187.15.234:8080/portal/record/recordTemplatePageList.action', '0', NULL, NULL, NULL, NULL, NULL,
                    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
                        update TF_SA_SEEP_HOLE set VULN_ID= $id where SEEP_HOLE_ID=$vid;

                   """, seep)

        list.append(message1)

    write_file('target/SATP_渗透报告_合规中心.sql', list)
